#!/usr/bin/env python
#coding:utf-8
'''  模块功能: 直接操作数据库创建群组 '''
import sys
import socket
import MySQLdb
import time
#import comm.global_config as global_config
#import ConfigParser as config

db = ''

#检查传参是否正常。
def check_argv():
	if len(sys.argv) != 2:
		print 'input right paramers'
		sys.exit()

#读取mysql的配置文件
def get_mysql_config():
		
	mysql_ip   		 = '192.168.5.253'
	mysql_port 		 = '3324'
	mysql_user 		 = 'work_w'
	mysql_passwd     = '12345qwert'
	mysql_db         = 'athena_baike'
	
	return mysql_ip, mysql_user, mysql_passwd, mysql_db, mysql_port

#初始化mysql连接,并返回
def init_mysql_db(mysql_ip, mysql_user, mysql_passwd, mysql_db, mysql_port):
	db = MySQLdb.connect(host = mysql_ip, user = mysql_user, passwd = mysql_passwd, db = mysql_db, port = int(mysql_port), charset='utf8')
	return db



'''
#执行sql语句
def db_execute(db, sql):
	if db is None or sql is None:
		return None
	try:
		cursor = db.cursor()
		cursor.execute(sql)
		db.commit()
	except pymysql.Error,e:
		print '%s ' % str(e)
		cursor.close()		
		return None	
'''


#执行sql语句并返回结果		
def db_execute_result(cursor, sql):
	if cursor is None or sql is None:
		return None

	try:
		cursor.execute(sql)
		data = cursor.fetchall()
		return data
	except:
		print "Error: unable to fecth data, sql=[%s]" % sql
		return None

# 参数说明：fields1为通用字段，fields2为具体音视频文件的字段
# 数据库返回后，fields1 的字段会直接放在info里，fields2会放在info[files]字段里
def _get_info_by_id(cursor, fields1, fields2, sql, type):
	results = db_execute_result(cursor, sql)
	if results is None or len(results)==0:
		return None

	infos = {}
	files	= []	#[ x[2] for x in results]
	for row in results:
		info = {}
		for i, val in enumerate(fields2):
			info[ fields2[i] ]	= row[len(fields1)+i]
		files.append(info)

	for i, val in enumerate(fields1):
		infos[ fields1[i] ]	= results[0][i]

	infos['type']		= type
	infos['files'] = files
	
	return infos


def get_video_info_by_id(cursor, id):
	# 可变sql拼接
	fields1 = ['res_globalid','res_entity_id','res_status','res_entity_type', 'vi_title']
	fields2 = ['awr_artist_id', 'awr_artist_name','vf_file_name','vi_status','vf_file_link','vf_definition','vf_aspect_ratio']

	sql = '''
		SELECT  %s,%s 
		FROM quku_resource as res, quku_video_info as info, quku_video_file as file, quku_artist_works_ref as artist 
		WHERE res.res_entity_id=info.vi_globalid AND info.vi_globalid=file.vf_videoinfo_id AND artist.awr_item_id=res.res_entity_id
		AND (file.vf_videoinfo_id='%s' OR file.vf_globalid='%s' OR file.vf_res_id='%s');
		''' % (','.join(fields1), ','.join(fields2), id, id, id)

	return _get_info_by_id(cursor, fields1, fields2, sql, 'mv');


def get_song_info_by_id(cursor, id):
	# 可变sql拼接
	fields1 = ['res_globalid','res_entity_id','res_status','res_entity_type', 'si_name']
	fields2 = ['awr_artist_id', 'awr_artist_name', 'af_res_id','af_file_bitrate','af_file_link']

	sql = '''
		SELECT %s,%s 
		FROM quku_resource as res, quku_song_info as info, quku_audio_file as file, quku_artist_works_ref as artist 
		WHERE res.res_entity_id=info.si_globalid AND info.si_globalid=file.af_song_id AND artist.awr_item_id=res.res_entity_id
		AND (file.af_song_id='%s' OR file.af_globalid='%s' OR file.af_res_id='%s');
		''' % (','.join(fields1), ','.join(fields2), id, id, id)

	return _get_info_by_id(cursor, fields1, fields2, sql, 'song');

#现在找不到组合的成员信息是怎么存储的
def get_aritst_info_by_id(cursor, id):
	fields1 = ['ai_globalid','ai_name','ai_gender','ai_area', 'ai_status','ai_yyr_id','ai_tinguid', 'ai_uniqcode']
	fields2 = ['amr_member_name', 'amr_member_id','amr_relation','amr_member_status']

	sql = '''
		SELECT %s,%s 
		FROM quku_artist_info as info LEFT JOIN quku_artist_member_ref as member ON info.ai_globalid=member.amr_artist_id
		WHERE (ai_globalid='%s' OR ai_yyr_id='%s' OR ai_tinguid='%s' OR amr_member_id='%s');
		''' % (','.join(fields1), ','.join(fields2), id, id, id, id)

	return _get_info_by_id(cursor, fields1, fields2, sql, 'artist');


def get_album_info_by_id(cursor, id):
	fields1 = ['ai_globalid','ai_name','ai_publishcompany','ai_art_aliasname', 'awr_artist_id','awr_artist_name']
	fields2 = []

	sql = '''
		SELECT %s
		FROM quku_album_info as info,  quku_artist_works_ref as artist 
		WHERE ai_globalid = '%s' AND artist.awr_item_id=info.ai_globalid;
		''' % (','.join(fields1), id)
	return _get_info_by_id(cursor, fields1, fields2, sql, 'album');


def get_info_by_id(id):
	#读取mysql配置文件,获取地址, 根据mysql地址初始化连接
	mysql_addr = get_mysql_config()
	db = init_mysql_db(mysql_addr[0], mysql_addr[1], mysql_addr[2], mysql_addr[3], mysql_addr[4])
	cursor = db.cursor()

	infos = get_aritst_info_by_id(cursor, id);
	if None == infos :
		infos = get_song_info_by_id(cursor, id);
		if None == infos :
			infos = get_video_info_by_id(cursor, id);
			if None == infos:
				infos = get_album_info_by_id(cursor, id);

	cursor.close()
	db.close()

	return infos

def main():
	#参数传入devl uid phone_num 
	check_argv()
	id       = sys.argv[1]

	infos = get_info_by_id(id)
	if None==infos:
		print '\n\t id=%s, not find !!!\n' % id
		return

	# 打印公共字段
	keys = infos.keys()
	keys.remove('files')
	for k in keys:
		print "\t %s : %s" % (k, infos[k])

	# 打印文件信息字段
	files = infos['files']
	if len(files)!=1 or infos['type']=='song' or infos['type']=='mv':
		for i in files:
			print "\t {"
			for k, v in i.items():
				print "\t\t %s : %s" % (k, v)
			print "\t }"
	
	return

	

if __name__ == '__main__':
	main()
	
	
	
	
	
	
	